package cn.com.ursaminor.mysql4j.core;

import java.lang.reflect.Method;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import cn.com.ursaminor.mysql4j.util.Mysql4jUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/*
	INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
	    [INTO] tbl_name
	    [PARTITION (partition_name [, partition_name] ...)]
	    [(col_name [, col_name] ...)]
	    {VALUES | VALUE} (value_list) [, (value_list)] ...
	    [AS row_alias[(col_alias [, col_alias] ...)]]
	    [ON DUPLICATE KEY UPDATE assignment_list]
	
	INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
	    [INTO] tbl_name
	    [PARTITION (partition_name [, partition_name] ...)]
	    [AS row_alias[(col_alias [, col_alias] ...)]]
	    SET assignment_list
	    [ON DUPLICATE KEY UPDATE assignment_list]
	
	INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
	    [INTO] tbl_name
	    [PARTITION (partition_name [, partition_name] ...)]
	    [(col_name [, col_name] ...)]
	    [AS row_alias[(col_alias [, col_alias] ...)]]
	    SELECT ...
	    [ON DUPLICATE KEY UPDATE assignment_list]
	
	value:
	    {expr | DEFAULT}
	
	value_list:
	    value [, value] ...
	
	assignment:
	    col_name = [row_alias.]value
	
	assignment_list:
	    assignment [, assignment] ... 
 */
/**
 * 该类拼接生成insert语句，并提交.
 * 
 * @author 小熊
 * @version 1.0
 */
public class Inserter
{
	private final static Logger logger = Logger.getLogger(Inserter.class);
	
	//-----------------------------------------------------------------------------------------------------------------
	
	private Table table;
	
	private StringBuffer insert;
	private StringBuffer cols;
	private StringBuffer values;
	
	private Query  query;
	
	/**
	 *	构造方法.
	 *
	 *	@param table 要插入数据的表
	 */
	public Inserter(Table table)
	{
		this.table = table;
		insert = new StringBuffer("insert into ").append(table.getName());
	}
	
	/**
	 *	设置字段值.
	 *
	 *	@param column 字段
	 *	@param value 值
	 *  @return 当前Inserter对象
	 */
    public Inserter set(Chr column, String value)
	{
		if(cols==null) cols = new StringBuffer(column.name);
		else cols.append(",").append(column.name);
		if(values==null) values = new StringBuffer(value==null?"null":("'"+value+"'"));
		else values.append(",").append(value==null?"null":("'"+value+"'"));
		return this;
	}
	
    /**
	 *	设置字段值.
	 *
	 *	@param column 字段
	 *	@param value 值
	 *  @return 当前Inserter对象
	 */
	public Inserter set(Num column, Number value)
	{
		if(cols==null) cols = new StringBuffer(column.name);
		else cols.append(",").append(column.name);
		if(values==null) values = new StringBuffer(value==null?"null":value.toString());
		else values.append(",").append(value==null?"null":value.toString());
		return this;
	}
	
	/**
	 *	设置字段值.
	 *
	 *	@param column 字段
	 *	@param value 值
	 *  @return 当前Inserter对象
	 */
	public Inserter set(Datetime column, Date value)
	{
		if(cols==null) cols = new StringBuffer(column.name);
		else cols.append(",").append(column.name);
		if(values==null) values = new StringBuffer(value==null?"null":("'"+Mysql4jUtil.formatDate(value)+"'"));
		else values.append(",").append(value==null?"null":("'"+Mysql4jUtil.formatDate(value)+"'"));
		return this;
	}
	
	/**
	 *	设置字段值.
	 *
	 *	@param column 字段
	 *	@param value 函数表达式
	 *  @return 当前Inserter对象
	 */
	public Inserter set(Column column, FunExpr value)
	{
		if(cols==null) cols = new StringBuffer(column.name);
		else cols.append(",").append(column.name);
		if(values==null) values = new StringBuffer(value==null?"null":value.toString());
		else values.append(",").append(value==null?"null":value.toString());
		return this;
	}
	
	/**
	 *	设置字段，用于insert into t … select … 语句中
	 *
	 *	@param columns 字段数组
	 *  @return 当前Inserter对象
	 */
	public Inserter columns(Column ... columns)
	{
		for(Column column : columns)
		{
			if(cols==null) cols = new StringBuffer(column.name);
			else cols.append(",").append(column.name);
		}
		return this;
	}
	
	/**
	 *	设置字段，用于insert into t … select … 语句中
	 *
	 *	@param query select查询语句
	 *  @return 当前Inserter对象
	 */
	public Inserter with(Query query)
	{
		this.query = query;
		return this;
	}
	
	/**
	 *	获得当前sql语句.
	 *
	 *  @return sql语句 
	 */
	public String getSql()
	{
		if(query==null) return new StringBuffer(insert).append(" (").append(cols).append(") values (").append(values).append(")").toString();
		else return new StringBuffer(insert).append(" (").append(cols).append(") ").append(query.getSql()).toString();
	}
	
	/**
	 *	提交sql.
	 *
	 *  @return 影响行数
	 */
	public int execute()
	{
		String sql = getSql();
		logger.info(sql);
		int r = table.getJdbcTemplate().update(sql);
		return r;
	}
	
	/**
	 *	提交sql，并获得新增数据的主键.
	 *
	 *	@param keyHolder 主键句柄，使用GeneratedKeyHolder即可
	 *  @return 影响行数
	 */
	public int execute(KeyHolder keyHolder)
	{
		String sql = getSql();
		logger.info(sql);
		return insertWithKeyHolder(table.getJdbcTemplate(), sql, keyHolder);
	}
	
	public String toString()
    {
    	return getSql();
    }
	
	// --静态快捷方法-----------------------------------------------------------------------------------------------------
	
	/**
	 *	获得插入pojo对象的sql语句.
	 *
	 *	@param table 表
	 *	@param pojo pojo对象，也可为Row对象
	 *  @return sql语句 
	 */
	public static String getSql(Table table, Object pojo)
	{
		StringBuffer cols = null;
		StringBuffer values = null;
		
		// INSERT INTO tbl_name (a,b,c) VALUES(1,2,3)
		for(int i=0; i<table.columns.size(); i++)
		{
			Column c = table.columns.get(i);
			
			if(c.increment) continue;
			
			if(cols==null) cols = new StringBuffer(c.name);
			else cols.append(",").append(c.name);
			
			try
			{
				Object obj = null;
				if(pojo instanceof Map)
				{
					obj = ((Map)pojo).get(c.getAliasOrName());
				}
				else
				{
					Method method = c.method;
					if(method!=null) obj = method.invoke(pojo, new Object[0]);
				}
				if(values==null)
				{
					if(c instanceof Chr) values = new StringBuffer(obj==null?"null":("'"+obj+"'"));
					else if(c instanceof Num) values = new StringBuffer(obj==null?"null":obj.toString());
					else if(c instanceof Datetime) values = new StringBuffer(obj==null?"null":("'"+Mysql4jUtil.formatDate((Date)obj)+"'"));
				}
				else
				{
					if(c instanceof Chr) values.append(",").append(obj==null?"null":("'"+obj+"'"));
					else if(c instanceof Num) values.append(",").append(obj==null?"null":obj.toString());
					else if(c instanceof Datetime) values.append(",").append(obj==null?"null":("'"+Mysql4jUtil.formatDate((Date)obj)+"'"));
				}
			}
			catch(Exception e)
			{
				logger.error(e);
			}
		}
		return "insert into " + table.getName() +" ("+cols+") values ("+values+")";
	}
	
	/**
	 *	把pojo数据插入到table表中，并自动设置新增数据的主键.
	 *
	 *	@param table 表
	 *  @param pojo 数据对象
	 *  @return 影响行数
	 */
	public static int insert(Table table, Object pojo)
	{
		String sql = getSql(table,pojo);
		logger.info(sql);
		
		if(table.primaryKey.isIncrement())
		{
			KeyHolder keyHolder = new GeneratedKeyHolder();
			int count = insertWithKeyHolder(table.getJdbcTemplate(), sql, keyHolder);
			if(pojo instanceof Map)
			{
				((Map)pojo).put(table.primaryKey.name, keyHolder.getKey());
			}
			else 
			{
				setPrimaryKey(pojo, table.primaryKey.property, keyHolder.getKey());
			}
			return count;
		}
		else
		{
			return table.getJdbcTemplate().update(sql);
		}
	}
	
	/**
	 *	把pojo数据列表插入到table表中，创建多条记录，并自动设置新增数据的主键.
	 *
	 *	@param table 表
	 *  @param pojos 数据对象列表
	 *  @return 影响行数
	 */
	public static int insert(Table table, List pojos)
	{
		if(pojos==null || pojos.size()==0) return 0;
		
		int result = 0;
		for(Object pojo: pojos)
		{
			result += insert(table, pojo);
		}
		return result;
	}
	
	// 设置主键
	private static void setPrimaryKey(Object pojo, String method, Number key)
	{
		Class cls = pojo.getClass();
		Class returnType = null;
		try
		{
			Method getMethod = cls.getMethod("get" + Mysql4jUtil.initialUpperCase(method), new Class[0]);
			returnType = getMethod.getReturnType();
			
			Method setMethod = cls.getMethod("set" + Mysql4jUtil.initialUpperCase(method), new Class[]{returnType});
			if(setMethod!=null)
			{
				setMethod.invoke(pojo, new Object[]{key});
			}
		}
		catch(Exception e)
		{
			logger.error(e);
			return;
		}
	}
	
	// 获得主键
	private static int insertWithKeyHolder(JdbcTemplate jdbcTemplate, String sql, KeyHolder keyHolder)
	{
		int count = jdbcTemplate.update(new PreparedStatementCreator()
		{
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException
			{
				return conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			}
		},
		keyHolder);
		return count;
	}
}
